package com.ttg.web.service.Impl;

import com.ttg.common.entities.BasePage;
import com.ttg.common.entities.PagesRes;
import com.ttg.common.enums.DimTypeEnum;
import com.ttg.common.enums.ErrorCodeEnums;
import com.ttg.common.exception.BaseException;
import com.ttg.common.utils.poi.ExcelUtil;
import com.ttg.model.dto.CMReportDTO;
import com.ttg.model.dto.CMReportYearMonthDTO;
import com.ttg.model.dto.QualityManageRuleListDTO;
import com.ttg.model.vo.*;
import com.ttg.web.dao.CostManageIndustryDepositLoanDao;
import com.ttg.web.dao.CostManageMerchDepositLoanDao;
import com.ttg.web.dao.QualityManageRuleListDao;
import com.ttg.web.service.CostManageService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.util.*;

/**
 * Description：
 * Author: chenyou
 * Date: 2025/9/11 10:20
 * Company: ttg
 * Copyright: Copyright (c) 2025
 */
@Slf4j
@Service
public class CostManageServiceImpl implements CostManageService {

    @Autowired
    private CostManageIndustryDepositLoanDao industryDepositLoanDao;
    @Autowired
    private CostManageMerchDepositLoanDao merchDepositLoanDao;

    private static final Integer PAGE_SIZE = 1000;

    @Override
    public List<String> depositSelectYears(CMReportYearMonthDTO reqDto) {
        if(DimTypeEnum.商户维度.getValue() == reqDto.getDimType()){
            return merchDepositLoanDao.selectYears();
        }else if(DimTypeEnum.行业维度.getValue() == reqDto.getDimType()){
            return industryDepositLoanDao.selectYears();
        }else{
            throw new BaseException(ErrorCodeEnums.QM_DIM_VAL_ERROR);
        }
    }

    @Override
    public List<String> depositSelectMonths(CMReportYearMonthDTO reqDto) {
        if(StringUtils.isBlank(reqDto.getYear())){
            throw new BaseException("年份不能为空");
        }
        if(DimTypeEnum.商户维度.getValue() == reqDto.getDimType()){
            return merchDepositLoanDao.selectMonths(reqDto.getYear());
        }else if(DimTypeEnum.行业维度.getValue() == reqDto.getDimType()){
            return industryDepositLoanDao.selectMonths(reqDto.getYear());
        }else{
            throw new BaseException(ErrorCodeEnums.QM_DIM_VAL_ERROR);
        }
    }

    @Override
    public BasePage<CMIndustryDepositVO> industryDeposit(CMReportDTO reqDto) {
        //参数检查
        dimTypeCheck(reqDto.getDimType());
        BasePage<QMIndustryReportVO> page = reqDto.parse();
        BasePage<CMIndustryDepositVO> voPage = industryDepositLoanDao.industryDeposit(page,reqDto);
        return voPage;
    }

    @Override
    public void industryDepositDownload(CMReportDTO reqDto, HttpServletResponse response) {
        //选中指定一级二级行业下载对应范围商户数的明细文件
        //参数：只需要一级二级行业
        Integer clsId1 = reqDto.getClsId1();
        Integer clsId2 = reqDto.getClsId2();
        if(clsId1==null||clsId2==null){
            throw new BaseException("请选择一级二级行业分类");
        }

        // 使用SXSSFWorkbook进行流式处理，优化内存使用
        SXSSFWorkbook workbook = new SXSSFWorkbook(ExcelUtil.ROW_ACCESS_WINDOW_SIZE);
        SXSSFSheet sheet = workbook.createSheet("行业商户存贷信息");
        // 创建并复用样式对象
        CellStyle[] styles = ExcelUtil.createReusableStyles(workbook);
        // 设置表头
        createMerchDepositHeader(sheet, styles);
        // 分批写入数据
        merchDepositDataInBatches(sheet, styles[3],reqDto);
        // 优化列宽设置
        ExcelUtil.safeAutoSizeColumns(sheet, 18); // 18列
        // 文件下载
        ExcelUtil.workbookExport(workbook,response,"行业商户存贷信息",ExcelUtil.FILE_TYPE_XLSX);
    }

    @Override
    public BasePage<CMMerchDepositVO> merchDeposit(CMReportDTO reqDto) {
        //参数检查
        dimTypeCheck(reqDto.getDimType());
        BasePage<CMMerchDepositVO> page = reqDto.parse();
        BasePage<CMMerchDepositVO> voPage = merchDepositLoanDao.merchDeposit(page,reqDto);
        return voPage;
    }

    @Override
    public void merchDepositDownload(CMReportDTO reqDto, HttpServletResponse response) {
        // 使用SXSSFWorkbook进行流式处理，优化内存使用
        SXSSFWorkbook workbook = new SXSSFWorkbook(ExcelUtil.ROW_ACCESS_WINDOW_SIZE);
        SXSSFSheet sheet = workbook.createSheet("商户存贷信息");
        // 创建并复用样式对象
        CellStyle[] styles = ExcelUtil.createReusableStyles(workbook);
        // 设置表头
        createMerchDepositHeader(sheet, styles);
        // 分批写入数据
        merchDepositDataInBatches(sheet, styles[3],reqDto);
        // 优化列宽设置
        ExcelUtil.safeAutoSizeColumns(sheet, 18); // 18列
        // 文件下载
        ExcelUtil.workbookExport(workbook,response,"商户存贷信息",ExcelUtil.FILE_TYPE_XLSX);
    }

    /**
     * 创建三层表头结构
     */
    private static void createMerchDepositHeader(Sheet sheet, CellStyle[] styles) {
        // 第一层表头（合并单元格）
        Row row1 = sheet.createRow(0);
        // 一层结构区域（独立单元格）
        ExcelUtil.createMergedHeaderCell(sheet, row1, 0, 0, 0, 0, "序列号", styles[0]);
        // 二层结构区域
        ExcelUtil.createMergedHeaderCell(sheet, row1, 1, 2, 0, 0, "行业", styles[0]);
        ExcelUtil.createMergedHeaderCell(sheet, row1, 3, 5, 0, 0, "商户情况", styles[0]);
        // 三层结构区域
        ExcelUtil.createMergedHeaderCell(sheet, row1, 6, 13, 0, 0, "存款情况", styles[0]);
        // 二层结构区域
        ExcelUtil.createMergedHeaderCell(sheet, row1, 14, 16, 0, 0, "贷款情况", styles[0]);
        // 一层结构区域（独立单元格）
        ExcelUtil.createMergedHeaderCell(sheet, row1, 17, 17, 0, 0, "指导分类", styles[0]);

        // 第二层表头
        Row row2 = sheet.createRow(1);
        // 一层结构区域没有第二层，留空但设置样式保持对齐
        row2.createCell(0).setCellValue("");
        row2.getCell(0).setCellStyle(styles[0]);
        // 二层结构区域的第二层（直接到具体字段）
        ExcelUtil.createMergedHeaderCell(sheet, row2, 1, 1, 1, 1, "主分类", styles[1]);
        ExcelUtil.createMergedHeaderCell(sheet, row2, 2, 2, 1, 1, "二级分类", styles[1]);
        ExcelUtil.createMergedHeaderCell(sheet, row2, 3, 3, 1, 1, "商户编码", styles[1]);
        ExcelUtil.createMergedHeaderCell(sheet, row2, 4, 4, 1, 1, "商户名称", styles[1]);
        ExcelUtil.createMergedHeaderCell(sheet, row2, 5, 5, 1, 1, "客户号", styles[1]);
        // 三层结构区域的第二层
        ExcelUtil.createMergedHeaderCell(sheet, row2, 6, 9, 1, 1, "直绑账户", styles[1]);
        ExcelUtil.createMergedHeaderCell(sheet, row2, 10, 13, 1, 1, "全量账户", styles[1]);
        // 二层结构区域的第二层（直接到具体字段）
        ExcelUtil.createMergedHeaderCell(sheet, row2, 14, 14, 1, 1, "授信规模", styles[1]);
        ExcelUtil.createMergedHeaderCell(sheet, row2, 15, 15, 1, 1, "用信规模", styles[1]);
        ExcelUtil.createMergedHeaderCell(sheet, row2, 16, 16, 1, 1, "存贷比", styles[1]);
        // 一层结构区域没有第二层，留空但设置样式保持对齐
        row2.createCell(17).setCellValue("");
        row2.getCell(17).setCellStyle(styles[0]);

        // 第三层表头（具体字段）
        Row row3 = sheet.createRow(2);
        String[] thirdLevelHeaders = {
                "","","","","","",
                "月日均规模", "月末时点规模",
                "偏离度", "加权利率",
                "月日均规模", "月末时点规模",
                "偏离度", "加权利率",
                "","","","",
        };
        // 第三层样式
        for(int i = 0; i < thirdLevelHeaders.length; i++) {
            Cell cell = row3.createCell(i);
            cell.setCellValue(thirdLevelHeaders[i]);
            // 只有三层结构区域设置第三层样式，其他区域使用对应层级样式
            if (i == 0 || i == 17) {
                cell.setCellStyle(styles[0]); // 一层结构的字段层
            } else if ((i>=1 && i <= 5) || (i>=14 && i<=16)) {
                cell.setCellStyle(styles[1]); // 二层结构的字段层
            } else {
                cell.setCellStyle(styles[2]); // 三层结构的第三层
            }
        }

        // 合并单元格处理
        // 一层结构区域：合并第1行和第3行
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
        // 二层结构区域：合并第1行和2行
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 14, 14));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 15, 15));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 16, 16));
        // 一层结构区域：合并第1行和第3行
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 17, 17));
    }

    /**
     * 分批写入商户存贷数据
     */
    private void merchDepositDataInBatches(Sheet sheet, CellStyle dataStyle,CMReportDTO reqDto) {
        reqDto.setPageSize(ExcelUtil.BATCH_SIZE);
        int extCount = 0;
        int total = 0;
        //数据起始行
        int startRow = 3;
        // 每次1000条，1000次就有100万，如果超过100w，xlsx也导不出来了，需要换方式，所以这里循环最多1000
        for (int i = 1; i <= 1000; i++) {
            reqDto.setPage(i);
            BasePage<CMMerchDepositVO> index = merchDeposit(reqDto);
            PagesRes pagesRes = index.getPagesRes();
            total = (int) pagesRes.getTotalnum();
            List<Object[]> batchData = merchDepositData(index.getList(), 18,extCount);
            extCount += CollectionUtils.isEmpty(index.getList()) ? 0 : index.getList().size();
            for (int j = 0; j < batchData.size(); j++) {
                Row row = sheet.createRow(startRow + j);
                Object[] rowData = batchData.get(j);

                for (int k = 0; k < rowData.length; k++) {
                    Cell cell = row.createCell(k);
                    ExcelUtil.setCellValue(cell, rowData[k]);
                    cell.setCellStyle(dataStyle);
                }

                // 定期刷新
                if (j % 500 == 0) {
                    try {
                        ((SXSSFSheet) sheet).flushRows(100);
                    } catch (Exception e) {
                        // 忽略刷新异常
                    }
                }
            }

            if (extCount >= total) {
                break;
            }
        }
    }

    /**
     * 组装商户存贷数据
     */
    private static List<Object[]> merchDepositData(List<CMMerchDepositVO> partList, int columns,int serialNumber) {
        List<Object[]> batchData = new ArrayList<>(ExcelUtil.BATCH_SIZE);

        if(CollectionUtils.isEmpty(partList)){
            return batchData;
        }
        for (int i=0;i<partList.size();i++) {
            CMMerchDepositVO vo = partList.get(i);
            Object[] rowData = new Object[columns];

            // 一层结构区域数据
            rowData[0] = serialNumber + i;
            // 二层结构区域数据
            rowData[1] = vo.getClsName1();
            rowData[2] = vo.getClsName2();
            rowData[3] = vo.getCmmdMctNo();
            rowData[4] = vo.getCmmdMctNo();
            rowData[5] = vo.getCmmdCustNo();
            // 三层结构区域数据
            rowData[6] = vo.getCmmdBindMonthAvgAmt();
            rowData[7] = vo.getCmmdBindMonthLastdayAmt();
            rowData[8] = vo.getCmmdDeviationDegree();
            rowData[9] = vo.getCmmdWeightRate();
            rowData[10] = vo.getCmmdMonthAvgAmt();
            rowData[11] = vo.getCmmdMonthLastdayAmt();
            rowData[12] = vo.getCmmdDeviationDegree();
            rowData[13] = vo.getCmmdWeightRate();
            // 二层结构区域数据
            rowData[14] = vo.getCmmdCreditAmt();
            rowData[15] = vo.getCmmdUseCreditAmt();
            rowData[16] = vo.getCmmdRatio();
            // 一层结构区域数据
            rowData[17] = vo.getQtrTypeName();
            batchData.add(rowData);
        }

        return batchData;
    }

    public void dimTypeCheck(Integer dimType){
        if(Objects.nonNull(dimType)){
            if(DimTypeEnum.getByVal(dimType)==null){
                throw new BaseException(ErrorCodeEnums.QM_DIM_VAL_ERROR);
            }
        }
    }
}
